package com.person.jc.service.test.certificate;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.person.jc.common.SQLSpell;
import com.person.jc.service.Service;
import com.person.jc.service.test.assign.BsTaskAssignService;
import com.person.jc.utils.Utils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by ailk on 2017/10/1.
 */
public class MakeService extends Service {
    public final static MakeService me = new MakeService();

    public List<Record> getList(Map<String, Object> map) {
        String temp = "";

        String tabName = "bs_repair_reg_log";
        String whereExt="";
        if("3".equals(map.get("orderType"))){
            tabName = "bs_recondition_log";
            whereExt=" and rl.original_record is not null  and rl.original_record_ok ='1'\n";
        }
        List<Object> params = new ArrayList<Object>();
        String tempSql = "SELECT \n" +
                "    o.order_id,\n" +
                "    o.receive_date,\n" +
                "    o.cust_id,\n" +
                "	("+ SQLSpell.customerName("o.cust_id")+") cust_name,\n" +
                "    oe.device_type,\n" +
                "	("+SQLSpell.kindName("device_type", "oe.device_type")+") device_type_name,\n" +
                "    COUNT(oe.device_no) num1,\n" +
                "    COUNT(rl.log_id) num2\n" +
                "FROM\n" +
                "    bs_order_equipment oe \n" +
                "        inner JOIN\n" +
                "    bs_order o ON oe.order_id = o.order_id\n" +
                "        inner JOIN\n" +
                "    "+tabName+" rl ON oe.equ_id = rl.equ_id\n" + whereExt +
                "WHERE\n" +
//                "    a.order_type = ?\n" +
                "     o.flow_id = ?\n";

        StringBuffer sql = new StringBuffer(tempSql);
//        params.add(map.get("orderType"));
        params.add(map.get("flow"));
//        if(Utils.isNotEmpty(temp = String.valueOf(map.get("optUser")))){
//            sql.append("AND au.user_id = ?\n");
//            params.add(temp);
//        }
        if(Utils.isNotEmpty(temp = String.valueOf(map.get("custId")))){
            sql.append("AND o.cust_id = ?\n");
            params.add(temp);
        }
        if(Utils.isNotEmpty(temp = String.valueOf(map.get("orderId")))){
            sql.append("AND o.order_id like ?\n");
            params.add("%"+temp+"%");
        }
        if(Utils.isNotEmpty(temp = String.valueOf(map.get("deviceType")))){
            sql.append("AND oe.device_type = ?\n");
            params.add(temp);
        }
        sql.append("GROUP BY o.order_id,o.cust_id , oe.device_type \n");
        sql.append("ORDER BY o.order_id,o.cust_id , oe.device_type");
        return Db.find(sql.toString(), Utils.getObjParam(params));
    }



    public List<Record> getViewList(Map<String, Object> map) {
        String temp = "";
        List<Object> params = new ArrayList<Object>();

        String tempSql = "SELECT DISTINCT\n" +
                "	 oe.*, ("+SQLSpell.userName("rl.check_user")+") check_user,\n ("+SQLSpell.userName("rl.opt_user")+") inspect_user, bc.save_path,\n " +
                "	 ("+SQLSpell.kindName("volume_category", "oe.volume_category")+") volume_category_,\n" +
                "	DATE_FORMAT(rl.opt_date, '%Y-%m-%d') inspect_date, rl.check_remark, rl.repair_remark, rl.address, rd.*,rcl.rec_id,rcl.remark rcl_remark,rcl.address rcl_address,rcl.original_record,rcl.original_record_ok,rcl.scopes,DATE_FORMAT(rcl.opt_date, '%Y-%m-%d') complate_date,rl.equ_id,\n" +
                "	 ("+SQLSpell.kindName("device_type", "oe.device_type")+") device_type_,\n" +
                "	 (SELECT group_concat(t.device_name) from bd_device_info t where FIND_IN_SET(t.device_id, oe.gas_type)) gas_type_,\n" +
                "	 ("+SQLSpell.kindName("yes_or_no", "oe.if_cert")+") if_cert_\n" +
                "FROM\n" +
                "    bs_order_equipment oe \n" +
                "        LEFT JOIN\n" +
                "    bs_order o ON oe.order_id = o.order_id\n" +
                "        LEFT JOIN\n" +
                "		bs_repair_reg_log rl ON oe.equ_id = rl.equ_id\n" +
                "        LEFT JOIN\n" +
                "    (select d.log_id, group_concat(distinct f.fault_name order by f.fault_name asc separator '; ') fault_name,group_concat(DISTINCT concat(p.parts_name, '[' , p.parts_code, ']') ORDER BY p.parts_name ASC, p.parts_code ASC separator '; ') parts_name from bs_repair_check_detail d left join bd_fault_info f on d.fault_id = f.fault_id left join bd_device_parts p on d.parts_id = p.parts_id group by d.log_id) rd on rd.log_id = rl.log_id\n" +
                "			 INNER JOIN\n" +
                "		bs_recondition_log rcl on rl.log_id = rcl.log_id\n" +
                "   left join  bs_certificate bc on bc.equ_id = oe.equ_id \n"+
                "WHERE o.flow_id = 6\n";
        StringBuffer sql = new StringBuffer(tempSql);

        if(Utils.isNotEmpty(temp = String.valueOf(map.get("orderId")))){
            sql.append("AND o.order_id = ?\n");
            params.add(temp);
        }
        if(Utils.isNotEmpty(temp = String.valueOf(map.get("custId")))){
            sql.append("AND o.cust_id = ?\n");
            params.add(temp);
        }
        if(Utils.isNotEmpty(temp = String.valueOf(map.get("deviceType")))){
            sql.append("AND oe.device_type = ?\n");
            params.add(temp);
        }
        if(Utils.isNotEmpty(temp = String.valueOf(map.get("equId")))){
            sql.append("AND oe.equ_id = ?\n");
            params.add(temp);
        }
        if(Utils.isNotEmpty(temp = String.valueOf(map.get("deviceNo")))){
            sql.append("AND oe.device_no like ?\n");
            params.add("%" + temp + "%");
        }
        sql.append("ORDER BY o.cust_id, oe.order_id");
        return Db.find(sql.toString(), Utils.getObjParam(params));
    }

    public int saveShadow(String orderId,String equId,String path){
        String sql = "update bs_certificate set shadow_address = ? where order_id = ? and equ_id=?";
        return Db.update(sql, path,orderId,equId);
    }

    public Record showShadow(String orderId,String equId){
        String sql="select  shadow_address from bs_certificate where order_id = ? and equ_id=? ";
        return Db.findFirst(sql,orderId,equId);
    }

    public Record findCertByOrderIdAndEquId(String orderId,String equId){
        String sql="select  * from bs_certificate where order_id = ? and equ_id=?  order by opt_date desc";
        return Db.findFirst(sql,orderId,equId);
    }

    public List<Record> findCertByOrderId(String orderId){
        String sql="SELECT a.* FROM ( SELECT order_id, equ_id, save_path FROM bs_certificate WHERE order_id = ? ORDER BY opt_date DESC ) a GROUP BY a.order_id, a.equ_id";
        return Db.find(sql,orderId);
    }

    public List<Record> findEquipmentByOrderIdAndType(String orderId,int deviceType){
        String sql="SELECT DISTINCT boe.* FROM bs_order_equipment boe INNER JOIN bs_certificate bc ON bc.equ_id = boe.equ_id WHERE boe.order_id = ? AND boe.device_type = ?";
        return Db.find(sql,orderId,deviceType);
    }

    public List<Record> findDeviceTypeByOrderId(String orderId){
        String sql="select DISTINCT device_type from bs_order_equipment where order_id=?";
        return Db.find(sql,orderId);
    }

    public Record findInfoByOrderId(String orderId){
        String sql="select bo.receive_date, bc.cust_name from bs_order bo inner join bd_customer bc on bc.cust_id= bo.cust_id where bo.order_id=?";
        return Db.findFirst(sql,orderId);
    }


}
